package org.abratuhi.uradar.modules;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

import org.abratuhi.uradar.model.URadarModule;
import org.abratuhi.uradar.model.URadarModuleModel;
import org.abratuhi.uradar.util.ResponseUtil;

/**
 * URadarModule for Second Life.
 * <p>
 * Serves for saving the information about position in Second Life of corresponding URadarID user
 * <p>
 * Contains 1 table:
 * <ul>
 * 	<li>(uradaid, moduleid, sl_name, sl_realm, sl_position, sl_status)
 * </ul>
 * @author Alexei Bratuhin
 * @version 1.00
 */
public class URadarModuleSL extends URadarModule{

	/**
	 * Constructor.
	 * <p>
	 * Here are the tables defined, and corresponding MySQL statements run.
	 * @param name			-	not important, is substituted with 'sl' in constructor
	 * @param description	-	not important, is substituted with 'Second Life update module' in constructor
	 * @param props			-	module specific properties, currently not really used
	 * @param connection	-	initialized connection to MySQL DB
	 */
	public URadarModuleSL(String name, String description, Properties props) {
		// super
		super("sl", "Second Life update module", props);
		// custom
		// specify the corresponding table
		String sl_name = "sl";
		String[] sl_fieldnames = {"uradarid", "moduleid", "sl_name", "sl_realm", "sl_position", "sl_status"};
		String[] sl_fieldtypes = {"varchar(100)", "varchar(100)", "varchar(100)", "varchar(100)", "varchar(100)", "varchar(100)"};
		URadarModuleModel sl_model = new URadarModuleModel(sl_name, sl_fieldnames, sl_fieldtypes);
		// add
		ArrayList<URadarModuleModel> modelz = new ArrayList<URadarModuleModel>();
		modelz.add(sl_model);
		this.setModel(modelz);
		// create tables if needed
		if(!this.checkTables()) this.createTables();
	}

	@Override
	public String proceedRequest(Properties reqprops) {
		// 
		openConnection();
		// get request type
		String reqtype = reqprops.getProperty("reqtype");
		// switch
		if(reqtype.equals("resolve_module_id")){
			return resolveModuleID(reqprops);
		}
		if(reqtype.equals("addupdate_user")){
			return addupdateUser(reqprops);
		}
		//return
		return null;

	}

	/**
	 * Add/update user in information in Second Life URadarModule.
	 * @param props		HTTP GET query
	 * @return			<ul><li>OK, if successfully added/update</li>
	 * 					<li>FAIL, otherwise</li></ul>
	 */
	public String addupdateUser(Properties props){
		// init response string
		String response = new String();
		// get info from obligatory fields
		String myURadarID = props.getProperty("uradarid");
		String mySLID = props.getProperty("moduleid");
		// get rest of info
		String mySLName = props.getProperty("sl_name");
		String mySLRealm = props.getProperty("sl_realm");
		String mySLPosition = props.getProperty("sl_position");
		String mySLStatus = props.getProperty("sl_status");
		// update MySQL DB
		try{
			Connection connection = openConnection();
			// create statement
			Statement stmt = connection.createStatement();
			// generate query string
			String sql_addupdate_user = null;
			String resolved = resolveModuleID(props);
			if(resolved.equals(INVALID_ID) || resolved.equals(FAIL)){
				if(resolved.equals(INVALID_ID)){	// -> add
					sql_addupdate_user = "insert into " +
										"sl(uradarid, moduleid, sl_name, sl_realm, sl_position, sl_status) " +
										"values ('"+myURadarID+"','"+mySLID+"','"+ mySLName+"','"+mySLRealm+"','"+mySLPosition+"','"+mySLStatus+"');";
				}
				if(resolved.equals(FAIL)){
					response = resolved;
				}
			}
			else{	// -> update
				sql_addupdate_user = "update sl set sl_realm='"+mySLRealm+"', sl_position='"+mySLPosition+"', sl_status='"+mySLStatus+"'" +
				" where uradarid='"+myURadarID+"';";
			}

			// execute query
			if(sql_addupdate_user!= null) stmt.executeUpdate(sql_addupdate_user);
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			closeConnection(connection);
			// 
			response = OK;
		}catch (SQLException e){
			e.printStackTrace();
			//
			response = FAIL;
		}
		// return
		return response;
	}

	/**
	 * Get information about user's Second Life position.
	 * @param myURadarID		URadarID of requester
	 * @return					<ul><li>XML-string representing module-specific information about user</li>
	 * 							<li>FAIL, if connection to DB couldn't be established</li></ul>
	 */
	public String getOwnInfo(String myURadarID){
		// init return/response string
		String out = new String();
		// try to connect to DB for search
		try{
			Connection connection = openConnection();
			
			// create statement
			Statement stmt = connection.createStatement();
			
			// generate query string
			String sql_get_own_info = 	"select * " +
										"from sl " +
										"where uradarid='"+myURadarID+"';";
			
			// query DB for own info
			ResultSet rs_get_own_info = stmt.executeQuery(sql_get_own_info);
			
			// convert result to xml
			out = ResponseUtil.convertResultSet2XMLString(rs_get_own_info);
			
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			
			closeConnection(connection);
		} catch (SQLException e){
			e.printStackTrace();
			out = FAIL;
		}
		// return
		return out;
	}

	/**
	 * Get information about user's Second Life position together with corresponding FBID.
	 * @param myURadarID		URadarID of requester
	 * @return					<ul><li>XML-string representing module-specific information about user</li>
	 * 							<li>FAIL, if connection to database couldn't be established</li></ul>
	 */
	public String getOwnInfoWithFB(String myURadarID){
		// init return/response string
		String out = new String();
		// try to connect to DB for search
		try{
			Connection connection = openConnection();
			
			// create statement
			Statement stmt = connection.createStatement();
			
			// generate query string
			String sql_get_own_info = 	"select sl.uradarid, sl.moduleid as slid, fb.moduleid as fbid, sl_name, sl_status, sl_realm, sl_position " +
										"from sl inner join fb on sl.uradarid = fb.uradarid " +
										"where sl.uradarid='"+myURadarID+"';";
			
			// query DB for own info
			ResultSet rs_get_own_info = stmt.executeQuery(sql_get_own_info);
			
			// generate return/response string
			out = ResponseUtil.convertResultSet2XMLString(rs_get_own_info);
			
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			
			closeConnection(connection);
		} catch (SQLException e){
			e.printStackTrace();
			out = FAIL;
		}
		// return
		return out;
	}

	/**
	 * Get information about user's friends position in Second Life.
	 * @param friendsURadarID		array of friends' URadarIDs
	 * @return						<ul><li>XML-string representing module-specific information about user's friends</li>
	 * 								<li>FAIL, if connection to database couldn't be established</li></ul>
	 */
	public String getFriendsInfo(String[] friendsURadarID){
		// init return/response string
		String out = new String();
		// try to connect to DB for search
		try{
			Connection connection = openConnection();
			
			// create statement
			Statement stmt = connection.createStatement();
			
			// generate query string
			String sql_get_friends_info = "";
			sql_get_friends_info += "select * from sl " +
									"where uradarid in ('";
			for(int i=0; i<friendsURadarID.length-1; i++){
				sql_get_friends_info += friendsURadarID[i] + "', '";
			}
			sql_get_friends_info += friendsURadarID[friendsURadarID.length-1];
			sql_get_friends_info += "');";
			
			// query DB for own info
			ResultSet rs_get_friends_info = stmt.executeQuery(sql_get_friends_info);
			
			// generate return/response string
			out = ResponseUtil.convertResultSet2XMLString(rs_get_friends_info);
			
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			
			closeConnection(connection);
		} catch (SQLException e){
			e.printStackTrace();
			out = FAIL;
		}
		// return
		return out;
	}
	
	/**
	 * Get information about user's friends position in Second Life together with corresponding FBID.
	 * @param friendsURadarID		array of friends' URadarIDs
	 * @return						<ul><li>XML-string representing module-specific information about user's friends</li>
	 * 								<li>FAIL, if connection to database couldn't be established</li></ul>
	 */
	public String getFriendsInfoWithFB(String[] friendsURadarID){
		// init return/response string
		String out = new String();
		//	check whether friends array is empty -> send empty response then		
		if(! (friendsURadarID.length > 0)){
			out = ResponseUtil.convertNothing2XMLString();
		}
		else{
			// try to connect to DB for search
			try{
				Connection connection = openConnection();
				
				// create statement
				Statement stmt = connection.createStatement();
				
				// generate query string
				String sql_get_friends_info = "";
				sql_get_friends_info += "select sl.uradarid, sl.moduleid as slid, fb.moduleid as fbid, sl_name, sl_status, sl_realm, sl_position " +
										"from sl inner join fb on sl.uradarid = fb.uradarid " +
										"where sl.uradarid in ('";
				for(int i=0; i<friendsURadarID.length-1; i++){
					sql_get_friends_info += friendsURadarID[i] + "',' ";
				}
				sql_get_friends_info += friendsURadarID[friendsURadarID.length-1];
				sql_get_friends_info += "');";
				
				// query DB for own info
				ResultSet rs_get_friends_info = stmt.executeQuery(sql_get_friends_info);
				
				// generate return/response string
				out = ResponseUtil.convertResultSet2XMLString(rs_get_friends_info);
				
				// close statement, garbage collector is not to be relied upon
				stmt.close();
				
				closeConnection(connection);
			} catch (SQLException e){
				e.printStackTrace();
				out = FAIL;
			}
		}
		// return
		return out;
	}

	/**
	 * Remove user from users of Second Life module.
	 * @param reqprops		HTTP GET query
	 * @return 				<ul><li>OK, if successfully removed user</li>
	 * 						<li>FAIL, otherwise</li></ul>
	 */
	public String removeUser(Properties reqprops){
		// get needed data from request
		String myURadarID = reqprops.getProperty("uradarid");
		// try to connect to DB for search
		try{
			Connection connection = openConnection();
			// create statement
			Statement stmt = connection.createStatement();
			// generate query strings
			String sql_delete_user = "delete  from sl where uradarid='"+myURadarID+"';";	// clean <modulname> table
			// query DB for own info
			stmt.executeUpdate(sql_delete_user);
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			closeConnection(connection);
			// return
			return OK;
		} catch (SQLException e){
			e.printStackTrace();
			return FAIL;
		}
	}

	
	@Override
	public String resolveModuleID(Properties reqprops) {
		// init return/response string
		String out = new String();
		// get request details
		String moduleID = reqprops.getProperty("moduleid");
		// try to connect to DB for search
		try{
			Connection connection = openConnection();
			// create statement
			Statement stmt = connection.createStatement();
			// generate query string
			String sql_check_moduleid = "select uradarid from sl where moduleid='"+moduleID+"';";
			// query DB, check whether moduleID isRegistered
			ResultSet rs_check = stmt.executeQuery(sql_check_moduleid);
			// check size of result -> if resultset is empty no resolution is possible
			if(rs_check.first()){
				out = rs_check.getString("uradarid");
			}
			else{ 
				out = INVALID_ID;
			}
			// close statement, garbage collector is not to be relied upon
			stmt.close();
			closeConnection(connection);

		} catch (SQLException e){
			e.printStackTrace();
			out = FAIL;
		}
		// return
		return out;
	}
}
